﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YXQ.Dao.ViewModel;
using AutoMapper;
using YXQ.Common.Extension;
using YXQ.Common.Helper;
using System.Data;
namespace YXQ.Dao
{

    public class ProjectDao
    {


        public ProjectDao()
        {

            Mapper.CreateMap<Y_Project, ProjectModel>();
        }
        public List<Y_Project> GetList(int startIndex, int maxRow, out int recordcount)
        {


            using (YXQManageEntities edm = new YXQManageEntities())
            {

                recordcount = edm.Y_Project.Count();

                return edm.Y_Project.OrderByDescending(s => s.Id).Skip(startIndex).Take(maxRow).ToList();

            }

        }


        public ProjectModel GetOneById(int id)
        {


            using (YXQManageEntities edm = new YXQManageEntities())
            {

                var query = edm.Y_Project.FirstOrDefault(s => s.Id == id);
                return Mapper.Map<Y_Project, ProjectModel>(query);
            }

        }


        public int Save(ProjectModel obj)
        {


            using (YXQManageEntities edm = new YXQManageEntities())
            {

                var query = edm.Y_Project.FirstOrDefault(s => s.Id == obj.Id);

                if (query == null)
                {


                    query = new Y_Project();
                    query.CreateTime = obj.CreateTime;
                    query.CreateRealName = obj.CreateRealName;
                    query.CreateUserId = obj.CreatUserId;
                    query.CreateUserName = obj.CreateUserName;
                    edm.AddToY_Project(query);

                }


                query.ProjectName = obj.ProjectName;
                query.Tpid = obj.Tpid;
                query.StartTime = obj.StartTime;
                query.EndTime = obj.EndTime;
                query.OrderId = obj.OrderId;
                query.IsDelete = obj.IsDelete;
                if (query.Id > 0) {
                    query.UpdateTime = obj.UpdateTime;
                    query.UpdateUserId = obj.UpdateUserId;
                    query.UpdateUserName = obj.UpdateUserName;
                    query.UpdateRealName = obj.UpdateRealName;
                }
              
                edm.SaveChanges();

                return query.Id;


            }

        }

        public List<ProjectModel> GetPlist(int tpid)
        {
            using (YXQManageEntities edm = new YXQManageEntities()) {
                return Mapper.Map<List<Y_Project>, List<ProjectModel>>(edm.Y_Project.Where(s=>s.Tpid==tpid&&s.IsDelete==0).OrderByDescending(s=>s.Id).ToList());
            }
        }
        /// <summary>
        /// 获取项目列表
        /// </summary>
        /// <param name="pname"></param>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <param name="startIndex"></param>
        /// <param name="maxRow"></param>
        /// <param name="tpid"></param>
        /// <param name="record"></param>
        /// <returns></returns>
        public List<ProjModel> GetPlist(string pname,string compName, string stime,string setime,string estime, string etime, int startIndex, int maxRow, int tpid, out int record)
        {
            var sql = @"select a.ProjectName,a.Id,a.Tpid,b.gcyf,b.gcyt,c.zbdw,zbje,zzsjje,a.CreateRealName,a.CreateTime,a.CreateUserName,
                        a.UpdateRealName,a.UpdateUserName,a.UpdateTime,a.StartTime,a.EndTime from  Y_Project a left join( 
                     select projectid,gcyf,gcyt from Y_CW
                    where Id in (select max(id) from Y_CW group by ProjectId) 
                    ) b
                    on a.Id=b.ProjectId

                    left join
                    (
	                    select projectid,zbdw,zbje  from Y_ZBB
                    where Id in (select max(id) from Y_ZBB group by ProjectId) 
                    ) c
                    on a.Id=c.ProjectId 
                    left join
                    (
	                    select projectid,zzsjje  from Y_YSJS
                    where Id in (select max(id) from Y_YSJS group by ProjectId) 
                    ) d
                    on a.Id=d.ProjectId
                    where 1=1 and a.IsDelete=0 
                    ";
            if (!string.IsNullOrEmpty(pname))
            {
                sql += " and projectname like '%"+pname+"%'";
            }
            if (!string.IsNullOrEmpty(compName)) {
                sql += " and zbdw like '%"+compName+"%'"; 
            }
            if (tpid > 0) {
                sql += " and a.tpid="+tpid;
            }
            if (!string.IsNullOrEmpty(stime)) {
                sql += " and startTime>='"+stime+"'";
            }
            if (!string.IsNullOrEmpty(setime))
            {
                sql += " and startTime<='" + setime + "'";
            }
            if (!string.IsNullOrEmpty(estime))
            {
                sql += " and endTime >='" + estime + "'";
            }
            if (!string.IsNullOrEmpty(etime))
            {
                sql += " and endTime <='" + etime + "'";
            }

            DataTable dt=  DBHelper.GetPageDataTable(sql,startIndex,maxRow,out record);

            if (dt.Rows.Count > 0) {
                var query = from s in dt.AsEnumerable()
                            select new ProjModel() {
                                Id = s.Field<int>("Id"),
                                Tpid=s.Field<int>("Tpid"),
                                ProjectName = s.Field<string>("ProjectName"),
                                CompName=s.Field<string>("zbdw"),
                                HtPrice = s.Field<decimal?>("zbje"),
                                SJrice = s.Field<decimal?>("zzsjje"),
                                Pay=s.Field<decimal?>("gcyf"),
                                BackPay=s.Field<decimal?>("gcyt"),
                                CreateRealName = s.Field<string>("CreateRealName"),
                                CreateTime = s.Field<DateTime?>("CreateTime"),
                                UpdateRealName = s.Field<string>("UpdateRealName"),
                                UpdateTime = s.Field<DateTime?>("UpdateTime"),
                                StartTime = s.Field<DateTime?>("StartTime"),
                                EndTime = s.Field<DateTime?>("EndTime") 
                            };
                return query.ToList();
            }

            return null;


        }

        public int DelById(int id)
        {


            using (YXQManageEntities edm = new YXQManageEntities())
            {

                var query = edm.Y_Project.FirstOrDefault(s => s.Id == id);

                query.IsDelete = 1;
                query.DeleteTime = DateTime.Now;


                return edm.SaveChanges();


            }

        }


    }

}

